{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bcbbd07f-f697-4582-98d9-e59a83388726",
   "metadata": {},
   "source": [
    "# **JSON and Pandas for Data Analysis**\n",
    "\n",
    "### **What's covered in this notebook?**\n",
    "\n",
    "1. JSON Structures\n",
    "    - Flat JSON\n",
    "    - Nested JSON (Hierarchical JSON)\n",
    "    - Multi-Level JSON (Deeply Nested JSON)\n",
    "2. Converting Flat JSON to DataFrame\n",
    "    - Using pd.DataFrame()\n",
    "    - Using pd.read_json()\n",
    "3. Handling Deeply Nested JSON Structures\n",
    "    - Normalizing Nested JSON Structures\n",
    "    - Normalizing Multi-Level JSON\n",
    "4. Few Examples\n",
    "\t- Example 1: Parse Students Data to Identify the Top Skill\n",
    "\t- Example 2: Parse Customer Transactions from JSON and Generate Insights\n",
    "\t- Example 3: Parse a Sample E-Commerce Order Data for Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12be0c9f-4d62-4a0e-8cc0-af7dc75a18ab",
   "metadata": {},
   "source": [
    "## **JSON Structures**\n",
    "\n",
    "JSON structures vary depending on how data is organized. Here we will try to understand differences between Flat, Nested, and Multi-Level JSON structure.\n",
    "\n",
    "### **Flat JSON**\n",
    "- Simple structure with no nesting.\n",
    "- Each key directly maps to a value.\n",
    "- Easy to parse and analyze in tabular formats (like DataFrames).\n",
    "- Best for tabular databases (SQL, CSV).\n",
    "- **How to Parse?** - We can directly parse it using pd.DataFrame() or pd.read_json() in pandas.\n",
    "```json\n",
    "{\n",
    "  \"student_id\": 101,\n",
    "  \"name\": \"Alice Johnson\",\n",
    "  \"age\": 21,\n",
    "  \"is_active\": true,\n",
    "  \"address_street\": \"123 Elm St\",\n",
    "  \"address_city\": \"New York\",\n",
    "  \"address_country\": \"USA\",\n",
    "  \"skills\": \"Python, SQL, Machine Learning\"\n",
    "}\n",
    "\n",
    "```\n",
    "\n",
    "### **Nested JSON (Hierarchical JSON)**\n",
    "- Data is stored in hierarchical format.\n",
    "- Values can be objects or arrays instead of primitive types.\n",
    "- More suitable for NoSQL databases (like MongoDB).\n",
    "- **How to Parse?** - We can parse them using pd.json_normalize(data, sep=\"_\").\n",
    "```json\n",
    "{\n",
    "  \"student_id\": 101,\n",
    "  \"name\": \"Alice Johnson\",\n",
    "  \"age\": 21,\n",
    "  \"is_active\": true,\n",
    "  \"address\": {\n",
    "    \"street\": \"123 Elm St\",\n",
    "    \"city\": \"New York\",\n",
    "    \"country\": \"USA\"\n",
    "  },\n",
    "  \"skills\": [\"Python\", \"SQL\", \"Machine Learning\"], \n",
    "  \"enrollment\": {\n",
    "      \"course\": \"Data Science\",\n",
    "      \"batch\": \"Spring 2025\",\n",
    "      \"grades\": {\n",
    "          \"assignments\": 92,\n",
    "          \"quizzes\": 88,\n",
    "          \"final_exam\": 95\n",
    "    }\n",
    "  }\n",
    "}\n",
    "\n",
    "```\n",
    "\n",
    "### **Multi-Level JSON (Deeply Nested JSON)**\n",
    "- Extends nested JSON by adding multiple levels of complexity.\n",
    "- Includes deeply nested objects and arrays within arrays.\n",
    "- More complex to parse and query.\n",
    "- **How to Parse?** - We can parse them using pd.json_normalize(data, record_path, meta_fields)\n",
    "\n",
    "\n",
    "```json\n",
    "{\n",
    "  \"student_id\": 101,\n",
    "  \"name\": \"Alice Johnson\",\n",
    "  \"age\": 21,\n",
    "  \"is_active\": true,\n",
    "  \"contact\": {\n",
    "    \"email\": \"alice@example.com\",\n",
    "    \"phone\": \"+1-234-567-8901\"\n",
    "  },\n",
    "  \"addresses\": [\n",
    "    {\n",
    "      \"type\": \"Home\",\n",
    "      \"street\": \"123 Elm St\",\n",
    "      \"city\": \"New York\",\n",
    "      \"country\": \"USA\"\n",
    "    },\n",
    "    {\n",
    "      \"type\": \"Temporary\",\n",
    "      \"street\": \"456 Oak Ave\",\n",
    "      \"city\": \"Los Angeles\",\n",
    "      \"country\": \"USA\"\n",
    "    }\n",
    "  ],\n",
    "  \"skills\": [\n",
    "    {\n",
    "      \"name\": \"Python\",\n",
    "      \"level\": \"Advanced\",\n",
    "      \"certified\": true\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"SQL\",\n",
    "      \"level\": \"Intermediate\",\n",
    "      \"certified\": false\n",
    "    }\n",
    "  ],\n",
    "  \"enrollment\": {\n",
    "    \"course\": \"Data Science\",\n",
    "    \"batch\": \"Spring 2025\",\n",
    "    \"grades\": {\n",
    "      \"assignments\": 92,\n",
    "      \"quizzes\": 88,\n",
    "      \"final_exam\": 95\n",
    "    }\n",
    "  }\n",
    "}\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8eee98a1-0ceb-4d94-8ca0-03fdf7e5c670",
   "metadata": {},
   "source": [
    "## **Converting Flat JSON to DataFrame**\n",
    "\n",
    "A flat JSON structure consists of a list of dictionaries, where each dictionary represents a row."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e706d1a1-42ce-44d4-ad61-94e357974dab",
   "metadata": {},
   "source": [
    "### **Using pd.DataFrame()**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f43bf3ea-2740-4141-957f-f1037162325e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\n",
      "    {\n",
      "        \"id\": 1,\n",
      "        \"name\": \"Alice\",\n",
      "        \"age\": 25,\n",
      "        \"city\": \"New York\"\n",
      "    },\n",
      "    {\n",
      "        \"id\": 2,\n",
      "        \"name\": \"Bob\",\n",
      "        \"age\": 30,\n",
      "        \"city\": \"San Francisco\"\n",
      "    },\n",
      "    {\n",
      "        \"id\": 3,\n",
      "        \"name\": \"Charlie\",\n",
      "        \"age\": 28,\n",
      "        \"city\": \"Los Angeles\"\n",
      "    }\n",
      "]\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "# Reading JSON file\n",
    "with open(\"data/flat_user_data.json\", \"r\") as file:\n",
    "    data = json.load(file)\n",
    "\n",
    "print(json.dumps(data, indent=4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9e75e169-dd77-4ac3-8fc0-26a9b26e6a08",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "      <td>25</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>San Francisco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>28</td>\n",
       "      <td>Los Angeles</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id     name  age           city\n",
       "0   1    Alice   25       New York\n",
       "1   2      Bob   30  San Francisco\n",
       "2   3  Charlie   28    Los Angeles"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Convert to Pandas DataFrame\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fa4a0bc-638c-40c0-a8ce-842d00e24e13",
   "metadata": {},
   "source": [
    "### **Using pd.read_json()**\n",
    "\n",
    "**pd.read_json(file_path)** Works Well Only for Simple JSON Structures.\n",
    "\n",
    "If the data is a **flat JSON structure** (i.e., a list of dictionaries where each dictionary represents a row), pd.read_json() works perfectly.\n",
    "\n",
    "Note that, this approach fails with deeply nested JSON."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8e81c2e2-bf4b-476b-89a6-442fec228d6a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "      <td>25</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Bob</td>\n",
       "      <td>30</td>\n",
       "      <td>San Francisco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>28</td>\n",
       "      <td>Los Angeles</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id     name  age           city\n",
       "0   1    Alice   25       New York\n",
       "1   2      Bob   30  San Francisco\n",
       "2   3  Charlie   28    Los Angeles"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_json(\"data/flat_user_data.json\")\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4e89f7f-4598-415c-8aba-a4b9855ccb54",
   "metadata": {},
   "source": [
    "## **Handling Deeply Nested JSON Structures**\n",
    "\n",
    "Complex nested JSON Structures requires preprocessing with **json.load(s) + pd.json_normalize()**.\n",
    "\n",
    "pd.json_normalize() helps unpack deeply nested JSON into a structured format.\n",
    "\n",
    "- **pd.json_normalize()** flattens nested JSON.\n",
    "- Use sep=\"_\" to customize column names for clarity."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd61b16a-ba1f-4915-a30f-cad34ff8a87f",
   "metadata": {},
   "source": [
    "### **Normalizing Nested JSON Structures**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f0c75132-d74c-4c05-afbf-1406571c693b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\n",
      "    {\n",
      "        \"id\": 1,\n",
      "        \"name\": \"Alice\",\n",
      "        \"address\": {\n",
      "            \"city\": \"New York\",\n",
      "            \"zip\": \"10001\"\n",
      "        }\n",
      "    },\n",
      "    {\n",
      "        \"id\": 2,\n",
      "        \"name\": \"Bob\",\n",
      "        \"address\": {\n",
      "            \"city\": \"San Francisco\",\n",
      "            \"zip\": \"94105\"\n",
      "        }\n",
      "    },\n",
      "    {\n",
      "        \"id\": 3,\n",
      "        \"name\": \"Charlie\",\n",
      "        \"address\": {\n",
      "            \"city\": \"Los Angeles\",\n",
      "            \"zip\": \"90001\"\n",
      "        }\n",
      "    }\n",
      "]\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "# Reading JSON file\n",
    "with open(\"data/nested_user_data.json\", \"r\") as file:\n",
    "    data = json.load(file)\n",
    "\n",
    "print(json.dumps(data, indent=4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "13499915-6a33-4c7f-8af0-b5105d2064bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "      <td>{'city': 'New York', 'zip': '10001'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Bob</td>\n",
       "      <td>{'city': 'San Francisco', 'zip': '94105'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>{'city': 'Los Angeles', 'zip': '90001'}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id     name                                    address\n",
       "0   1    Alice       {'city': 'New York', 'zip': '10001'}\n",
       "1   2      Bob  {'city': 'San Francisco', 'zip': '94105'}\n",
       "2   3  Charlie    {'city': 'Los Angeles', 'zip': '90001'}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert to Pandas DataFrame\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5a562138-f385-4c3b-88e6-d350b555bdc7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>address_city</th>\n",
       "      <th>address_zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "      <td>New York</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Bob</td>\n",
       "      <td>San Francisco</td>\n",
       "      <td>94105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>90001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id     name   address_city address_zip\n",
       "0   1    Alice       New York       10001\n",
       "1   2      Bob  San Francisco       94105\n",
       "2   3  Charlie    Los Angeles       90001"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Flatten nested fields\n",
    "df = pd.json_normalize(data, sep=\"_\")  \n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01b3ba20-3274-4727-9a1c-b107f540997d",
   "metadata": {},
   "source": [
    "### **Normalizing Multi-Level JSON**\n",
    "\n",
    "**pd.json_normalize(data, record_path, meta)** expands nested lists into rows. This is particularly useful when dealing with deeply nested JSON data.\n",
    "\n",
    "**Syntax**\n",
    "```python\n",
    "pd.json_normalize(data, record_path, meta)\n",
    "```\n",
    "\n",
    "- \"data\" - The JSON like object (a dict or list of dict)\n",
    "- \"record_path\" - Must be a list or null.\n",
    "- \"meta\" - A list of keys whose values should be included as metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4efa6920-079f-4e29-ac2f-82040da88462",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\n",
      "    {\n",
      "        \"id\": 1,\n",
      "        \"name\": \"Alice\",\n",
      "        \"orders\": [\n",
      "            {\n",
      "                \"order_id\": 101,\n",
      "                \"amount\": 250\n",
      "            },\n",
      "            {\n",
      "                \"order_id\": 102,\n",
      "                \"amount\": 150\n",
      "            }\n",
      "        ]\n",
      "    },\n",
      "    {\n",
      "        \"id\": 2,\n",
      "        \"name\": \"Bob\",\n",
      "        \"orders\": [\n",
      "            {\n",
      "                \"order_id\": 103,\n",
      "                \"amount\": 300\n",
      "            }\n",
      "        ]\n",
      "    }\n",
      "]\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "# Reading JSON file\n",
    "with open(\"data/customer_multi_level.json\", \"r\") as file:\n",
    "    data = json.load(file)\n",
    "\n",
    "print(json.dumps(data, indent=4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7b9e7447-9900-436f-804a-c57a8503ca83",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>orders</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "      <td>[{'order_id': 101, 'amount': 250}, {'order_id'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Bob</td>\n",
       "      <td>[{'order_id': 103, 'amount': 300}]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id   name                                             orders\n",
       "0   1  Alice  [{'order_id': 101, 'amount': 250}, {'order_id'...\n",
       "1   2    Bob                 [{'order_id': 103, 'amount': 300}]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert JSON to DataFrame\n",
    "df_users = pd.DataFrame(data)\n",
    "\n",
    "df_users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1e84edc4-a53e-4471-b3f9-619f30318cac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>250</td>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>150</td>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>300</td>\n",
       "      <td>2</td>\n",
       "      <td>Bob</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  amount id   name\n",
       "0       101     250  1  Alice\n",
       "1       102     150  1  Alice\n",
       "2       103     300  2    Bob"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Normalize orders into a separate DataFrame\n",
    "df_orders = pd.json_normalize(data, record_path=[\"orders\"], meta=[\"id\", \"name\"])\n",
    "\n",
    "df_orders"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42feb47e-68f3-4675-b38a-6893c22ad4ee",
   "metadata": {},
   "source": [
    "## **Few Examples**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6c0a5c3-fe94-4753-9eb9-0488a07bdc60",
   "metadata": {},
   "source": [
    "### **Example 1: Parse Students Data to Identify the Top Skill**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "826c6501-40b9-42aa-8996-72fdd29d980a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\n",
      "    {\n",
      "        \"id\": 1,\n",
      "        \"name\": \"Alice\",\n",
      "        \"skills\": [\n",
      "            \"Python\",\n",
      "            \"SQL\",\n",
      "            \"Machine Learning\"\n",
      "        ]\n",
      "    },\n",
      "    {\n",
      "        \"id\": 2,\n",
      "        \"name\": \"Bob\",\n",
      "        \"skills\": [\n",
      "            \"Java\",\n",
      "            \"Python\"\n",
      "        ]\n",
      "    },\n",
      "    {\n",
      "        \"id\": 3,\n",
      "        \"name\": \"Charlie\",\n",
      "        \"skills\": [\n",
      "            \"Python\",\n",
      "            \"JavaScript\",\n",
      "            \"Machine Learning\"\n",
      "        ]\n",
      "    }\n",
      "]\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "# Reading JSON file\n",
    "with open(\"data/student_skills.json\", \"r\") as file:\n",
    "    data = json.load(file)\n",
    "\n",
    "print(json.dumps(data, indent=4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "dd9e5b52-13de-483a-90df-65b6a201b4ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>skills</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "      <td>[Python, SQL, Machine Learning]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Bob</td>\n",
       "      <td>[Java, Python]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>[Python, JavaScript, Machine Learning]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id     name                                  skills\n",
       "0   1    Alice         [Python, SQL, Machine Learning]\n",
       "1   2      Bob                          [Java, Python]\n",
       "2   3  Charlie  [Python, JavaScript, Machine Learning]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert to DataFrame\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "122fd346-16cd-4ac0-87d5-06faf3875f01",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>skills</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "      <td>Python</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "      <td>SQL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "      <td>Machine Learning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>Bob</td>\n",
       "      <td>Java</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>Bob</td>\n",
       "      <td>Python</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>Python</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>JavaScript</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>Machine Learning</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id     name            skills\n",
       "0   1    Alice            Python\n",
       "1   1    Alice               SQL\n",
       "2   1    Alice  Machine Learning\n",
       "3   2      Bob              Java\n",
       "4   2      Bob            Python\n",
       "5   3  Charlie            Python\n",
       "6   3  Charlie        JavaScript\n",
       "7   3  Charlie  Machine Learning"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new = df.explode(\"skills\").reset_index(drop=True)\n",
    "\n",
    "df_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e793ae37-6bae-450c-99b0-5db8bf35f42c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "skills\n",
       "Python              3\n",
       "Machine Learning    2\n",
       "SQL                 1\n",
       "Java                1\n",
       "JavaScript          1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new[\"skills\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06a82ec8-db99-4a93-9335-1331212e5e0e",
   "metadata": {},
   "source": [
    "### **Example 2: Parse Customer Transactions from JSON and Generate Insights**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "3c1c902c-882e-48e6-be4e-94112862b14b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\n",
      "    {\n",
      "        \"customer\": \"Alice\",\n",
      "        \"amount\": 200,\n",
      "        \"date\": \"2024-03-01\"\n",
      "    },\n",
      "    {\n",
      "        \"customer\": \"Bob\",\n",
      "        \"amount\": 150,\n",
      "        \"date\": \"2024-03-02\"\n",
      "    },\n",
      "    {\n",
      "        \"customer\": \"Alice\",\n",
      "        \"amount\": 300,\n",
      "        \"date\": \"2024-03-05\"\n",
      "    },\n",
      "    {\n",
      "        \"customer\": \"Charlie\",\n",
      "        \"amount\": 500,\n",
      "        \"date\": \"2024-03-06\"\n",
      "    },\n",
      "    {\n",
      "        \"customer\": \"Bob\",\n",
      "        \"amount\": 100,\n",
      "        \"date\": \"2024-03-07\"\n",
      "    }\n",
      "]\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "# Reading JSON file\n",
    "with open(\"data/ecom_transaction_1.json\", \"r\") as file:\n",
    "    data = json.load(file)\n",
    "\n",
    "print(json.dumps(data, indent=4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "15d4ee77-02af-45b1-a19c-cafb06fc0d2c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer</th>\n",
       "      <th>amount</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alice</td>\n",
       "      <td>200</td>\n",
       "      <td>2024-03-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bob</td>\n",
       "      <td>150</td>\n",
       "      <td>2024-03-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alice</td>\n",
       "      <td>300</td>\n",
       "      <td>2024-03-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Charlie</td>\n",
       "      <td>500</td>\n",
       "      <td>2024-03-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bob</td>\n",
       "      <td>100</td>\n",
       "      <td>2024-03-07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  customer  amount       date\n",
       "0    Alice     200 2024-03-01\n",
       "1      Bob     150 2024-03-02\n",
       "2    Alice     300 2024-03-05\n",
       "3  Charlie     500 2024-03-06\n",
       "4      Bob     100 2024-03-07"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(data)\n",
    "\n",
    "# Convert date column to datetime\n",
    "df[\"date\"] = pd.to_datetime(df[\"date\"])\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "76b63aac-2a2d-4582-abe7-1b7e1658994f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Spending:\n",
      "   customer  amount\n",
      "0    Alice     500\n",
      "1      Bob     250\n",
      "2  Charlie     500\n",
      "\n",
      "Average Spending:\n",
      "   customer  amount\n",
      "0    Alice   250.0\n",
      "1      Bob   125.0\n",
      "2  Charlie   500.0\n",
      "\n",
      "Most Frequent Customer: Alice\n"
     ]
    }
   ],
   "source": [
    "# Calculate total spending per customer\n",
    "total_spending = df.groupby(\"customer\")[\"amount\"].sum().reset_index()\n",
    "\n",
    "# Calculate average transaction amount per customer\n",
    "average_spending = df.groupby(\"customer\")[\"amount\"].mean().reset_index()\n",
    "\n",
    "# Find the most frequent customer\n",
    "frequent_customer = df[\"customer\"].value_counts().idxmax()\n",
    "\n",
    "print(\"Total Spending:\\n\", total_spending)\n",
    "print(\"\\nAverage Spending:\\n\", average_spending)\n",
    "print(\"\\nMost Frequent Customer:\", frequent_customer)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "668da3e4-c53c-4fd7-998f-ffe6e6b10ef8",
   "metadata": {},
   "source": [
    "### **Example 3: Parse a Sample E-Commerce Order Data for Analysis**\n",
    "\n",
    "Assume that you received a **multi-level deeply nested JSON data** from an API. Your task is to **extract specific fields efficiently** and **flatten it for analysis**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "85cf112f-36a2-45ad-9e7a-9f10efe4674a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\n",
      "    {\n",
      "        \"order_id\": \"ORD1\",\n",
      "        \"customer\": {\n",
      "            \"id\": 1639,\n",
      "            \"name\": \"Angela Griffin\",\n",
      "            \"email\": \"gibbsedward@example.org\",\n",
      "            \"address\": {\n",
      "                \"street\": \"24026 Darlene Ranch\",\n",
      "                \"city\": \"Angelashire\",\n",
      "                \"country\": \"Saint Helena\"\n",
      "            }\n",
      "        },\n",
      "        \"items\": [\n",
      "            {\n",
      "                \"product_id\": 25,\n",
      "                \"name\": \"Mountain Bike\",\n",
      "                \"category\": \"Sports\",\n",
      "                \"price\": 599.99,\n",
      "                \"quantity\": 1\n",
      "            },\n",
      "            {\n",
      "                \"product_id\": 23,\n",
      "                \"name\": \"Tennis Racket\",\n",
      "                \"category\": \"Sports\",\n",
      "                \"price\": 89.99,\n",
      "                \"quantity\": 2\n",
      "            }\n",
      "        ],\n",
      "        \"payment\": {\n",
      "            \"method\": \"Credit Card\",\n",
      "            \"transaction_id\": \"D005B042-A\",\n",
      "            \"discount_applied\": 2.48\n",
      "        }\n",
      "    },\n",
      "    {\n",
      "        \"order_id\": \"ORD2\",\n",
      "        \"customer\": {\n",
      "            \"id\": 163,\n",
      "            \"name\": \"Sarah Moore\",\n",
      "            \"email\": \"zshelton@example.org\",\n",
      "            \"address\": {\n",
      "                \"street\": \"33466 Kristin Meadow Suite 060\",\n",
      "                \"city\": \"Lake Brittany\",\n",
      "                \"country\": \"Nigeria\"\n",
      "            }\n",
      "        },\n",
      "        \"items\": [\n",
      "            {\n",
      "                \"product_id\": 24,\n",
      "                \"name\": \"Hydration Backpack for Runners\",\n",
      "                \"category\": \"Sports\",\n",
      "                \"price\": 59.99,\n",
      "                \"quantity\": 1\n",
      "            },\n",
      "            {\n",
      "                \"product_id\": 21,\n",
      "                \"name\": \"Yoga Mat with Non-Slip Surface\",\n",
      "                \"category\": \"Sports\",\n",
      "                \"price\": 39.99,\n",
      "                \"quantity\": 2\n",
      "            }\n",
      "        ],\n",
      "        \"payment\": {\n",
      "            \"method\": \"Credit Card\",\n",
      "            \"transaction_id\": \"C1A2BCA7-1\",\n",
      "            \"discount_applied\": 6.12\n",
      "        }\n",
      "    },\n",
      "    {\n",
      "        \"order_id\": \"ORD3\",\n",
      "        \"customer\": {\n",
      "            \"id\": 1814,\n",
      "            \"name\": \"Dwayne Hartman\",\n",
      "            \"email\": \"daviddyer@example.org\",\n",
      "            \"address\": {\n",
      "                \"street\": \"32036 Rodney Creek\",\n",
      "                \"city\": \"New Brandy\",\n",
      "                \"country\": \"Sierra Leone\"\n",
      "            }\n",
      "        },\n",
      "        \"items\": [\n",
      "            {\n",
      "                \"product_id\": 28,\n",
      "                \"name\": \"Noise-Canceling Office Headset\",\n",
      "                \"category\": \"Accessories\",\n",
      "                \"price\": 89.99,\n",
      "                \"quantity\": 1\n",
      "            }\n",
      "        ],\n",
      "        \"payment\": {\n",
      "            \"method\": \"Credit Card\",\n",
      "            \"transaction_id\": \"98F4114D-B\",\n",
      "            \"discount_applied\": 5.42\n",
      "        }\n",
      "    }\n",
      "]\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "# Reading JSON file\n",
    "with open(\"data/ecom_transaction_2.json\", \"r\") as file:\n",
    "    data = json.load(file)\n",
    "\n",
    "print(json.dumps(data, indent=4))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "1bd4c74c-3695-4863-a20e-9459edeed21f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>customer</th>\n",
       "      <th>items</th>\n",
       "      <th>payment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>{'id': 1639, 'name': 'Angela Griffin', 'email'...</td>\n",
       "      <td>[{'product_id': 25, 'name': 'Mountain Bike', '...</td>\n",
       "      <td>{'method': 'Credit Card', 'transaction_id': 'D...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>{'id': 163, 'name': 'Sarah Moore', 'email': 'z...</td>\n",
       "      <td>[{'product_id': 24, 'name': 'Hydration Backpac...</td>\n",
       "      <td>{'method': 'Credit Card', 'transaction_id': 'C...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD3</td>\n",
       "      <td>{'id': 1814, 'name': 'Dwayne Hartman', 'email'...</td>\n",
       "      <td>[{'product_id': 28, 'name': 'Noise-Canceling O...</td>\n",
       "      <td>{'method': 'Credit Card', 'transaction_id': '9...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_id                                           customer  \\\n",
       "0     ORD1  {'id': 1639, 'name': 'Angela Griffin', 'email'...   \n",
       "1     ORD2  {'id': 163, 'name': 'Sarah Moore', 'email': 'z...   \n",
       "2     ORD3  {'id': 1814, 'name': 'Dwayne Hartman', 'email'...   \n",
       "\n",
       "                                               items  \\\n",
       "0  [{'product_id': 25, 'name': 'Mountain Bike', '...   \n",
       "1  [{'product_id': 24, 'name': 'Hydration Backpac...   \n",
       "2  [{'product_id': 28, 'name': 'Noise-Canceling O...   \n",
       "\n",
       "                                             payment  \n",
       "0  {'method': 'Credit Card', 'transaction_id': 'D...  \n",
       "1  {'method': 'Credit Card', 'transaction_id': 'C...  \n",
       "2  {'method': 'Credit Card', 'transaction_id': '9...  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading data to DF\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fb6fb8ab-ea2a-4406-af03-52ad891f6f79",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>items</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>customer_email</th>\n",
       "      <th>customer_address_street</th>\n",
       "      <th>customer_address_city</th>\n",
       "      <th>customer_address_country</th>\n",
       "      <th>payment_method</th>\n",
       "      <th>payment_transaction_id</th>\n",
       "      <th>payment_discount_applied</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>[{'product_id': 25, 'name': 'Mountain Bike', '...</td>\n",
       "      <td>1639</td>\n",
       "      <td>Angela Griffin</td>\n",
       "      <td>gibbsedward@example.org</td>\n",
       "      <td>24026 Darlene Ranch</td>\n",
       "      <td>Angelashire</td>\n",
       "      <td>Saint Helena</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>D005B042-A</td>\n",
       "      <td>2.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>[{'product_id': 24, 'name': 'Hydration Backpac...</td>\n",
       "      <td>163</td>\n",
       "      <td>Sarah Moore</td>\n",
       "      <td>zshelton@example.org</td>\n",
       "      <td>33466 Kristin Meadow Suite 060</td>\n",
       "      <td>Lake Brittany</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>C1A2BCA7-1</td>\n",
       "      <td>6.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD3</td>\n",
       "      <td>[{'product_id': 28, 'name': 'Noise-Canceling O...</td>\n",
       "      <td>1814</td>\n",
       "      <td>Dwayne Hartman</td>\n",
       "      <td>daviddyer@example.org</td>\n",
       "      <td>32036 Rodney Creek</td>\n",
       "      <td>New Brandy</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>98F4114D-B</td>\n",
       "      <td>5.42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_id                                              items  customer_id  \\\n",
       "0     ORD1  [{'product_id': 25, 'name': 'Mountain Bike', '...         1639   \n",
       "1     ORD2  [{'product_id': 24, 'name': 'Hydration Backpac...          163   \n",
       "2     ORD3  [{'product_id': 28, 'name': 'Noise-Canceling O...         1814   \n",
       "\n",
       "    customer_name           customer_email         customer_address_street  \\\n",
       "0  Angela Griffin  gibbsedward@example.org             24026 Darlene Ranch   \n",
       "1     Sarah Moore     zshelton@example.org  33466 Kristin Meadow Suite 060   \n",
       "2  Dwayne Hartman    daviddyer@example.org              32036 Rodney Creek   \n",
       "\n",
       "  customer_address_city customer_address_country payment_method  \\\n",
       "0           Angelashire             Saint Helena    Credit Card   \n",
       "1         Lake Brittany                  Nigeria    Credit Card   \n",
       "2            New Brandy             Sierra Leone    Credit Card   \n",
       "\n",
       "  payment_transaction_id  payment_discount_applied  \n",
       "0             D005B042-A                      2.48  \n",
       "1             C1A2BCA7-1                      6.12  \n",
       "2             98F4114D-B                      5.42  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.json_normalize(data, sep=\"_\")\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3c413365-8a52-4499-b87e-dbb44f4e48ac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>payment_method</th>\n",
       "      <th>payment_transaction_id</th>\n",
       "      <th>payment_discount_applied</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>D005B042-A</td>\n",
       "      <td>2.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>C1A2BCA7-1</td>\n",
       "      <td>6.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD3</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>98F4114D-B</td>\n",
       "      <td>5.42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_id payment_method payment_transaction_id  payment_discount_applied\n",
       "0     ORD1    Credit Card             D005B042-A                      2.48\n",
       "1     ORD2    Credit Card             C1A2BCA7-1                      6.12\n",
       "2     ORD3    Credit Card             98F4114D-B                      5.42"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_payments = df[[\"order_id\", \"payment_method\", \"payment_transaction_id\", \n",
    "                  \"payment_discount_applied\"]].copy()\n",
    "\n",
    "df_payments.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "4bb3c7ec-c01a-4d75-b065-cac91c1f0bd6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customer_id</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>customer_email</th>\n",
       "      <th>customer_address_street</th>\n",
       "      <th>customer_address_city</th>\n",
       "      <th>customer_address_country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1639</td>\n",
       "      <td>Angela Griffin</td>\n",
       "      <td>gibbsedward@example.org</td>\n",
       "      <td>24026 Darlene Ranch</td>\n",
       "      <td>Angelashire</td>\n",
       "      <td>Saint Helena</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>163</td>\n",
       "      <td>Sarah Moore</td>\n",
       "      <td>zshelton@example.org</td>\n",
       "      <td>33466 Kristin Meadow Suite 060</td>\n",
       "      <td>Lake Brittany</td>\n",
       "      <td>Nigeria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1814</td>\n",
       "      <td>Dwayne Hartman</td>\n",
       "      <td>daviddyer@example.org</td>\n",
       "      <td>32036 Rodney Creek</td>\n",
       "      <td>New Brandy</td>\n",
       "      <td>Sierra Leone</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customer_id   customer_name           customer_email  \\\n",
       "0         1639  Angela Griffin  gibbsedward@example.org   \n",
       "1          163     Sarah Moore     zshelton@example.org   \n",
       "2         1814  Dwayne Hartman    daviddyer@example.org   \n",
       "\n",
       "          customer_address_street customer_address_city  \\\n",
       "0             24026 Darlene Ranch           Angelashire   \n",
       "1  33466 Kristin Meadow Suite 060         Lake Brittany   \n",
       "2              32036 Rodney Creek            New Brandy   \n",
       "\n",
       "  customer_address_country  \n",
       "0             Saint Helena  \n",
       "1                  Nigeria  \n",
       "2             Sierra Leone  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_customers = df[[\"customer_id\", \"customer_name\", \"customer_email\", \n",
    "                  \"customer_address_street\", \"customer_address_city\", \n",
    "                   \"customer_address_country\"]].copy()\n",
    "\n",
    "df_customers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "baa7e5bd-fbbc-4f32-822e-bc5b2f798ce9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category</th>\n",
       "      <th>price</th>\n",
       "      <th>quantity</th>\n",
       "      <th>order_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25</td>\n",
       "      <td>Mountain Bike</td>\n",
       "      <td>Sports</td>\n",
       "      <td>599.99</td>\n",
       "      <td>1</td>\n",
       "      <td>ORD1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23</td>\n",
       "      <td>Tennis Racket</td>\n",
       "      <td>Sports</td>\n",
       "      <td>89.99</td>\n",
       "      <td>2</td>\n",
       "      <td>ORD1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24</td>\n",
       "      <td>Hydration Backpack for Runners</td>\n",
       "      <td>Sports</td>\n",
       "      <td>59.99</td>\n",
       "      <td>1</td>\n",
       "      <td>ORD2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>Yoga Mat with Non-Slip Surface</td>\n",
       "      <td>Sports</td>\n",
       "      <td>39.99</td>\n",
       "      <td>2</td>\n",
       "      <td>ORD2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>Noise-Canceling Office Headset</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>89.99</td>\n",
       "      <td>1</td>\n",
       "      <td>ORD3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id                            name     category   price  quantity  \\\n",
       "0          25                   Mountain Bike       Sports  599.99         1   \n",
       "1          23                   Tennis Racket       Sports   89.99         2   \n",
       "2          24  Hydration Backpack for Runners       Sports   59.99         1   \n",
       "3          21  Yoga Mat with Non-Slip Surface       Sports   39.99         2   \n",
       "4          28  Noise-Canceling Office Headset  Accessories   89.99         1   \n",
       "\n",
       "  order_id  \n",
       "0     ORD1  \n",
       "1     ORD1  \n",
       "2     ORD2  \n",
       "3     ORD2  \n",
       "4     ORD3  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_order_items = pd.json_normalize(data, \n",
    "                                  sep=\"_\", \n",
    "                                  record_path=[\"items\"], \n",
    "                                  meta=[\"order_id\"]\n",
    "                                  )\n",
    "\n",
    "df_order_items.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
